import openpyxl
import tools_pmc
import os
import win32ui
import win32con
import time
import math
from openpyxl.utils import get_column_letter
from tqdm import tqdm

desktop_path = os.path.join(os.environ['USERPROFILE'], 'Desktop')


# noinspection PyShadowingNames
def yc_demo():
    print("是否生成新的数据导入模板?(Y/N)")
    temp = input()
    if temp == "y" or temp == "Y":
        wb = openpyxl.Workbook()
        ws = wb.worksheets[0]
        ws.cell(1, 1).value = "品号（格式要求：数值）"
        ws.cell(1, 2).value = "预测数量（格式要求：数值）"
        ws.cell(1, 3).value = "预测月份（格式要求：常规）"
        ws.column_dimensions['A'].width = 25
        ws.column_dimensions['B'].width = 25
        ws.column_dimensions['C'].width = 25
        try:
            wb.save(desktop_path.replace('\\', "/") + "/" + "销售预测导入模板.xlsx")
            print("模板已生成！\n路径：", desktop_path.replace('\\', "/") + "/" + "销售预测导入模板.xlsx")
        except PermissionError:
            print("文件已打开，请关闭后重新运行！")
            yc_demo()


def cfdlg(sn: str):
    open_flags = win32con.OFN_FILEMUSTEXIST | win32con.OFN_PATHMUSTEXIST
    fspec = "BOM表 (*.xlsx)|*.xlsx||"
    dlg = win32ui.CreateFileDialog(1, None, sn + ".xlsx", open_flags, fspec)  # 1表示打开文件对话框
    dlg.SetOFNTitle("导入 " + sn + " BOM")
    dlg.DoModal()
    return dlg.GetPathName().replace('\\', "/")


def vlookup_23(lookup_value, table_array):
    temp2 = [0, 0]
    for row in table_array:
        if str(row[0].value) == str(lookup_value):
            if row[1].value is not None:
                temp2[0] = row[1].value
            if row[2].value is not None:
                temp2[1] = row[2].value
    return temp2


print("——————PMCtool_物料抛转 v1.0——————\n")
yc_demo()
input("预测导入完成后按任意键继续！")
wb_yc = openpyxl.load_workbook(desktop_path.replace('\\', "/") + "/" + "销售预测导入模板.xlsx")
ws_yc = wb_yc.worksheets[0]
wb_bom = openpyxl.load_workbook(tools_pmc.lj_bom)
ws_bom = wb_bom.worksheets[0]
wb_wlxxb = openpyxl.load_workbook(tools_pmc.lj_wlxxb)
ws_wlxxb = wb_wlxxb["物料信息表" + tools_pmc.today_str1]

list1 = []
for i in tqdm(range(2, ws_yc.max_row + 1), desc="预测导入中"):
    temp = False
    for j in range(2, ws_bom.max_row + 1):
        if str(ws_yc.cell(i, 1).value) == ws_bom.cell(j, 1).value:
            temp = True
            break
    if not temp:
        list1.append(str(ws_yc.cell(i, 1).value))
list1 = list(set(list1))
print("无BOM品号：", list1)
for i in list1:
    print(i, "Y状态BOM缺失，请手动导出“尾阶材料用量”!")
    temp = input('BOM导出后按任意键继续(N不导入)->')
    if temp != "n" and temp != "N":
        wb = openpyxl.load_workbook(cfdlg(i))
        list_bom_1_erp = tools_pmc.bom_1_erp(wb.worksheets[0])
        temp = ws_bom.max_row + 1
        for j in range(0, len(list_bom_1_erp[0])):
            ws_bom.cell(temp, 1).value = i
            ws_bom.cell(temp, 3).value = list_bom_1_erp[0][j]
            ws_bom.cell(temp, 4).value = list_bom_1_erp[1][j]
            temp += 1
        print(i, "BOM导入成功！\n")
    else:
        print(i, "BOM未导入！\n")

wb1 = openpyxl.Workbook()
ws1 = wb1.worksheets[0]  # 物料需求对应来源成品表
wb2 = openpyxl.Workbook()
ws2 = wb2.worksheets[0]  # 物料需求月度分解表表

start_time = time.time()
list_start1_1 = tools_pmc.bom_start_1(ws_bom)
a = 2
for i in tqdm(range(2, ws_yc.max_row + 1), desc="抛转进度  "):
    list_bom_1 = tools_pmc.bom_1(str(ws_yc.cell(i, 1).value), ws_bom)
    for j in range(0, len(list_bom_1[0])):
        list1 = [list_bom_1[0][j],
                 math.ceil(list_bom_1[1][j] * ws_yc.cell(i, 2).value * 20) / 20,
                 str(ws_yc.cell(i, 1).value),
                 ws_yc.cell(i, 2).value,
                 ws_yc.cell(i, 3).value]
        list2 = [1, 4, 5, 8, 9]
        for k in range(0, len(list2)):
            ws1.cell(a, list2[k]).value = list1[k]
        a += 1

# 对照表
table_array_wlxxb1 = list(ws_wlxxb['A2':'Z' + str(ws_wlxxb.max_row)])
table_array_wlxxb2 = list(ws_wlxxb['A11988':'Z' + str(ws_wlxxb.max_row)])
for i in tqdm(range(2, ws1.max_row + 1), desc="对照表进度"):
    list1 = vlookup_23(ws1.cell(i, 1).value, table_array_wlxxb1)
    ws1.cell(i, 2).value = list1[0]
    ws1.cell(i, 3).value = list1[1]

    list1 = vlookup_23(ws1.cell(i, 5).value, table_array_wlxxb2)
    if list1 is None:
        list1 = [0, 0]

    ws1.cell(i, 6).value = list1[0]
    ws1.cell(i, 7).value = list1[1]
list1 = ["原材料品号", "原材料品名", "原材料规格", "需求数量", "成品品号", "成品品名", "成品规格", "需求数量",
         "需求月份"]
list2 = [15, 26, 35, 10, 15, 26, 35, 10, 20]
for i in range(0, len(list1)):
    ws1.cell(1, i + 1).value = list1[i]
    ws1.cell(1, i + 1).fill = tools_pmc.fill_blue
    ws1.column_dimensions[get_column_letter(i + 1)].width = list2[i]

# 物料需求表
set1 = set()  # 品号
set2 = set()  # 月份
for i in range(2, ws1.max_row + 1):
    set1.add(ws1.cell(i, 1).value)
    set2.add(ws1.cell(i, 9).value)

list1 = list(set1)
list1.sort()
list2 = list(set2)
list2.sort()
list_add = list2
list_result = [list1]
for i in range(1, len(list2) + 1):
    list_result.append([0] * len(list1))

for i in range(2, ws1.max_row + 1):
    list_result[list2.index(ws1.cell(i, 9).value) + 1][list1.index(ws1.cell(i, 1).value)] += ws1.cell(i, 4).value
for i in tqdm(range(0, len(list_result[0])), desc="需求表进度"):
    ws2.cell(i + 2, 1).value = list_result[0][i]
    list_temp = vlookup_23(list_result[0][i], table_array_wlxxb1)
    ws2.cell(i + 2, 2).value = list_temp[0]
    ws2.cell(i + 2, 3).value = list_temp[1]
    for j in range(0, len(list2)):
        ws2.cell(i + 2, 4 + j).value = list_result[j + 1][i]
end_time = time.time()

list2 = [15, 26, 35]
list1 = ["品号", "品名", "规格"]
for i in list_add:
    list1.append(i)
    list2.append(15)
for i in range(0, len(list1)):
    ws2.cell(1, i + 1).value = list1[i]
    ws2.cell(1, i + 1).fill = tools_pmc.fill_blue
    ws2.column_dimensions[get_column_letter(i + 1)].width = list2[i]

ws_yc.insert_cols(2, 2)
for i in range(2, ws_yc.max_row + 1):
    ws_yc.cell(i, 2).value = tools_pmc.vlookup(ws_yc.cell(i, 1).value, tools_pmc.table_array_wlxxb, 2)
    ws_yc.cell(i, 3).value = tools_pmc.vlookup(ws_yc.cell(i, 1).value, tools_pmc.table_array_wlxxb, 3)
ws_yc.cell(1, 2).value = "品名"
ws_yc.cell(1, 3).value = "规格"

# str1 = "2024.12-2025.02销售月滚动"
# str1 = "2025年度预测"
str1 = "销售预测毛需求04-06月 - 核算2025年度预测需求物料"
wb_yc.save(desktop_path.replace('\\', "/") + "/" + str1 + "销售滚动预测需求明细表.xlsx")
wb1.save(desktop_path.replace('\\', "/") + "/" + str1 + "滚动物料需求对应销售需求.xlsx")
wb2.save(desktop_path.replace('\\', "/") + "/" + str1 + "滚动物料需求明细表.xlsx")

print("抛转结束，总用时：", int(end_time - start_time), "s")
input("输入任意键结束！")
